#coding:utf-8
"""
操作数据库
"""
import sqlite3
import hashlib

class database():
    def __init__(self):
        self.conn=None
        self.connect()

    def __del__(self):
        try:
            self.close()
        except:
            pass

    def connect(self):
        self.conn=sqlite3.connect('webmanage.db')

    def execute(self,sql):
        return self.conn.execute(sql)

    def commit(self):
        self.conn.commit()

    def close(self):
        self.conn.close()

    def insert_place(self,place,unique=True):
        """unique=True place字段值唯一"""
        place=place.strip()
        if unique:
            cursor=self.execute(r"SELECT place from place where place = '%s'"%(place))
            count=len(cursor.fetchall())
            if count:
                return None
        cursor=self.execute(r"INSERT INTO place (place) VALUES ('%s')"%(place))
        self.commit()
        return cursor


    def insert_project(self,project,unique=True):
        project=project.strip()
        if unique:
            cursor=self.execute(r"SELECT project from project where project = '%s'"%(project))
            count=len(cursor.fetchall())
            if count:
                return None
        cursor=self.execute(r"INSERT INTO project (project) VALUES ('%s')"%(project))
        self.commit()
        return cursor

    def insert_sa(self,sa,unique=True):
        sa=sa.strip()
        if unique:
            cursor=self.execute(r"SELECT sa from sa where sa = '%s'"%(sa))
            count=len(cursor.fetchall())
            if count:
                return None
        cursor=self.execute(r"INSERT INTO sa (sa) VALUES ('%s')"%(sa))
        self.commit()
        return cursor

    def insert_system(self,system,unique=True):
        system=system.strip()
        if unique:
            cursor=self.execute(r"SELECT system from system where system = '%s'"%(system))
            count=len(cursor.fetchall())
            if count:
                return None
        cursor=self.execute(r"INSERT INTO system (system) VALUES ('%s')"%(system))
        self.commit()
        return cursor

    def insert_type(self,type,unique=True):
        type=type.strip()
        if unique:
            cursor=self.execute(r"SELECT type from type where type = '%s'"%(type))
            count=len(cursor.fetchall())
            if count:
                return None
        cursor=self.execute(r"INSERT INTO type (type) VALUES ('%s')"%(type))
        self.commit()
        return cursor

    def check_user(self,username,password):
        """认证登录信息"""
        username=username.strip()
        password=password.strip()
        cursor=self.execute(r"SELECT password_md5 FROM userinfo WHERE username='%s'"%(username))
        rows=cursor.fetchall()
        if rows:
            print rows
            if hashlib.md5(password).hexdigest()==rows[0][0]:
                return True
        return False


    def init_database(self):
        """
        初始化数据库
        """
        self.execute(r'''CREATE TABLE servicer (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                loginstr TEXT,
                place INT,
                system INT,
                project INT,
                sa INT,
                ip TEXT,
                comment TEXT,
                type INT
                );''')

        self.execute('''CREATE TABLE place (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                place TEXT);''')

        self.execute('''CREATE TABLE system (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                system TEXT);''')

        self.execute('''CREATE TABLE project (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project TEXT);''')
    
        self.execute('''CREATE TABLE sa (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sa TEXT);''')
    
        self.execute('''CREATE TABLE type (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                 type TEXT);''')

        self.execute('''CREATE TABLE userinfo (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                 username TEXT,
                 password_md5 TEXT);''')

if __name__=='__main__':
    #db=database()
    #db.init_database()
    #cursor=db.execute(r"INSERT INTO place (place) VALUES ('法国电信')")
    #db.commit()
    #cursor=db.execute('SELECT * from servicer')
    #for info in cursor:
    #    print info
    #db.close()
    pass